import sqlite3
from datetime import datetime, date, timedelta
from utils.config_manager import config


def create_sh_jl_table(db_path="robot_zl.db"):
    """创建 sh_jl 表"""
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
              CREATE TABLE IF NOT EXISTS resume_library (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                userId INTEGER NOT NULL,
                create_time TEXT NOT NULL,
                name TEXT NOT NULL,
                ctno TEXT NOT NULL,
                datajson TEXT NOT NULL
            )
        ''')
        conn.commit()


def get_today_date():
    """获取当前日期"""
    return date.today().strftime("%Y-%m-%d")


def get_or_create_record(zwno, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """获取或创建记录，使用当天日期"""
    current_date2 = get_today_date()

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT id, cs, complate FROM sh_jl 
            WHERE user_id = ? AND current_date2 = ? AND zwno = ?
        ''', (user_id, current_date2, zwno))

        result = cursor.fetchone()

        if result:
            return result[0], result[1], result[2]

        create_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor.execute('''
            INSERT INTO sh_jl (user_id, create_time, current_date2, zwno, cs, complate)
            VALUES (?, ?, ?, ?, 0, FALSE)
        ''', (user_id, create_time, current_date2, zwno))
        conn.commit()
        return cursor.lastrowid, 0, False


def increment_cs(zwno, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """增加 cs 计数"""
    record_id, cs, complate = get_or_create_record(zwno, user_id, db_path)

    if not complate:
        new_cs = cs + 1
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE sh_jl 
                SET cs = ? 
                WHERE id = ?
            ''', (new_cs, record_id))
            conn.commit()
        return new_cs
    return cs


def mark_complete(zwno, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """标记为完成，并记录完成时间"""
    record_id, cs, complate = get_or_create_record(zwno, user_id, db_path)

    if not complate:
        complate_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        with sqlite3.connect(db_path) as conn:
            cursor = conn.cursor()
            cursor.execute('''
                UPDATE sh_jl 
                SET complate = TRUE, complate_time = ? 
                WHERE id = ?
            ''', (complate_time, record_id))
            conn.commit()
    return True


def get_last_record_of_day(user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """获取当天最后一条记录的 zwno、complate 和 cs"""
    current_date2 = get_today_date()

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT zwno, complate, cs
            FROM sh_jl 
            WHERE current_date2 = ? AND user_id = ?
            ORDER BY create_time DESC 
            LIMIT 1
        ''', (current_date2, user_id))

        result = cursor.fetchone()

        if result:
            return {
                "zwno": result[0],
                "complate": result[1],
                "cs": result[2]
            }
        return None


def is_over_20_minutes(zwno, min=20, user_id=1, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """判断某条记录的 create_time 与当前时间是否超过指定分钟数"""
    current_date2 = get_today_date()

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT create_time FROM sh_jl 
            WHERE user_id = ? AND sh_jl.current_date2 = ? AND zwno = ?
        ''', (user_id, current_date2, zwno))

        result = cursor.fetchone()

        if not result:
            return False

        create_time_str = result[0]
        create_time = datetime.strptime(create_time_str, "%Y-%m-%d %H:%M:%S")
        current_time = datetime.now()

        time_diff = current_time - create_time
        return time_diff > timedelta(minutes=min)


def get_next_zwno(position_list, db_path="robot_zl.db"):
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """
    判断当前应该处理哪个 zwno
    返回: 下一个要处理的 zwno，或者 None（如果已到列表末尾）
    """
    position_list = sorted(position_list, key=lambda x: int(x["name"]))
    today = get_today_date()

    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        # 获取当天最大的 zwno 及其 complate 状态
        cursor.execute('''
            SELECT zwno, complate 
            FROM sh_jl 
            WHERE current_date2 = ? 
            ORDER BY zwno DESC 
            LIMIT 1
        ''', (today,))
        result = cursor.fetchone()

        # 如果当天没有记录，从列表第一个开始
        if not result:
            return position_list[0]["name"]

        # 获取最大 zwno 和 complate 状态
        current_zwno, is_complete = result

        # 查找当前 zwno 在排序后的列表中的位置
        max_zwno_index = -1
        for i, pos in enumerate(position_list):
            if int(pos["name"]) == current_zwno:
                max_zwno_index = i
                break

        if max_zwno_index == -1:
            raise ValueError(f"数据库中的 zwno {current_zwno} 不在职位列表中")

        # 如果未完成，返回当前 zwno
        if not is_complete:
            return current_zwno

        # 如果已完成，找下一个
        next_index = max_zwno_index + 1
        if next_index < len(position_list):
            return position_list[next_index]["name"]
        else:
            return None  # 已到列表末尾

def get_today_cs_sum(db_path="robot_zl.db") -> int:
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """计算当天所有记录的 cs 总和"""
    today = get_today_date()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT SUM(cs) 
            FROM sh_jl 
            WHERE current_date2 = ?
        ''', (today,))
        result = cursor.fetchone()
        return result[0] if result[0] is not None else 0


def get_currentzw_cs_sum(zwno, db_path="robot_zl.db") -> int:
    # 使用config获取数据库路径
    db_path =  config.get_db_path()
    """计算当天所有记录的 cs 总和"""
    today = get_today_date()
    with sqlite3.connect(db_path) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            SELECT cs 
            FROM sh_jl 
            WHERE current_date2 = ? AND  zwno = ?
        ''', (today,zwno))
        result = cursor.fetchone()
        return result[0] if result[0] is not None else 0



create_sh_jl_table()

# 使用示例
if __name__ == "__main__":
    # 创建表
    create_sh_jl_table()

    # 示例操作
    zwno = 1001

    # 增加 cs
    print(f"CS after increment: {increment_cs(zwno)}")
    print(f"CS after increment: {increment_cs(zwno)}")

    # 获取最后一条记录
    last_record = get_last_record_of_day()
    print(f"Last record: {last_record}")

    # 检查是否超过20分钟
    print(f"Is over 20 minutes: {is_over_20_minutes(zwno)}")

    # 标记为完成
    mark_complete(zwno)
    print(f"Last record after complete: {get_last_record_of_day()}")